﻿using System;
using System.Collections.Generic;
using System.Text;
using DBAccess;
using System.Data;

namespace SD.Database
{
   public  class DataAccess
    {
       public static DataSet GetBattlesForPlayer(int pId)
       {
           return DAL.GetInstance().GetData("SELECT Distinct (SELECT PlayerName FROM Players WHERE (PlayerId = Battles.AttackerID)) AS Attacker, Battles.AttackerCV, (SELECT PlayerName FROM Players AS Players_2 WHERE (PlayerId = Battles.DefenderId)) AS Defender, Battles.DefenderCV, Battles.Date, Systems.SystemName AS System, Battles.PlanetId AS Planet, (SELECT PlayerName FROM Players AS Players_3 WHERE (PlayerId = Battles.WinnerId)) AS Winner FROM Systems INNER JOIN Battles ON Systems.SystemID = Battles.SystemId CROSS JOIN Players AS Players_1 WHERE (Battles.AttackerID = @param0) OR (Battles.DefenderId = @param0) ORDER BY Battles.Date DESC", new List<object>() { pId });
       }
       public static DataSet GetIntelForPlayer(int pId)
       {
           return DAL.GetInstance().GetData("SELECT Players.PlayerName, Intel.DateOfScan, Intel.Attack, Intel.Defense, Intel.Speed, Intel.Culture, Intel.Production, Intel.Science, Intel.Growth, Intel.Biology, Intel.Economy, Intel.Energy, Intel.Math, Intel.Physics, Intel.Social, Intel.CurrentCulture FROM Intel INNER JOIN Players ON Players.PlayerId = Intel.pId1 WHERE (Intel.pId = @param0)", new List<object>() { pId });
       }
       public static DataSet GetTAs(int pId)
       {
           return DAL.GetInstance().GetData("SELECT (SELECT PlayerName FROM Players WHERE (PlayerId = Agreements.pId1)) AS \"From\", (SELECT PlayerName FROM Players AS Players_1 WHERE (PlayerId = Agreements.pId2)) AS \"To\", Status, TypeA as \"Type\" FROM Agreements WHERE (TypeA = 0) AND (pId1 = @param0) OR (TypeA = 0) AND (pId2 = @param0)", new List<object>() { pId });
       }
       public static DataSet GetSAs(int pId)
       {
           return DAL.GetInstance().GetData("SELECT (SELECT PlayerName FROM Players WHERE (PlayerId = Agreements.pId1)) AS \"From\", (SELECT PlayerName FROM Players AS Players_1 WHERE (PlayerId = Agreements.pId2)) AS \"To\", Status, TypeA as \"Type\" FROM Agreements WHERE (TypeA = 1) AND (pId1 = @param0) OR (TypeA = 1) AND (pId2 = @param0)", new List<object>() { pId });
       }
       public static DataSet GetAllPlayers()
       {
           return DAL.GetInstance().GetData("SELECT [PlayerId], [PlayerName] FROM [Players] WHERE ([PlayerId] > 1)");
       }
       public static DataSet GetFleetsOnAir(int pId)
       {//TODO: add time zone support here
           return DAL.GetInstance().GetData("SELECT ColonyShips, Transports, Crusiers, Destroyers, Battleships, FromSystem, FromPlanet, ToPlanet, ToSystem, ArrivialTime AS \"Time\" FROM Fleets WHERE (OwnerID = @param0) ORDER BY Time DESC", new List<object>() { pId });
       }
       public static DataSet GetFleetsOnPlanets(int pID)
       {
           return DAL.GetInstance().GetData(@"SELECT Planets.SystemID, Planets.PlanetID, CAST(Planets.ColonyShips AS int) AS ColonyShips, CAST(Planets.Transports AS int) AS Transports, CAST(Planets.Destroyers AS int) AS Destroyers, CAST(Planets.Battleships AS int) AS Battleships, CAST(Planets.Crusiers AS int) AS Cruisers, Systems.SystemName + ' #' + CAST(Planets.PlanetID AS nvarchar) AS Expr1 FROM Planets INNER JOIN Systems ON Planets.SystemID = Systems.SystemID WHERE 
            (Planets.FleetOwnerID = @param0) AND (CAST(Planets.ColonyShips AS int) + CAST(Planets.Transports AS int) + CAST(Planets.Destroyers AS int) + CAST(Planets.Battleships AS int) + CAST(Planets.Crusiers AS int) > 0) 
            OR (Planets.FleetOwnerID = 0) AND (CAST(Planets.ColonyShips AS int) + CAST(Planets.Transports AS int) + CAST(Planets.Destroyers AS int) + CAST(Planets.Battleships AS int) + CAST(Planets.Crusiers AS int) > 0) AND (Planets.OwnerID = @param0)", new List<object>() { pID });
       }
       public static DataSet GetSystemsInRange(int pId)
       {
           return DAL.GetInstance().GetData(@"SELECT SystemID, SystemName, X, Y FROM Systems WHERE 
                (SQRT(POWER(X - (SELECT X FROM Systems AS Systems_2 WHERE (SystemID = (SELECT OrginID FROM Players WHERE (PlayerId = @param0)))), 2) 
                + POWER(Y - (SELECT Y FROM Systems AS Systems_1 WHERE (SystemID = (SELECT OrginID FROM Players AS Players_2 WHERE (PlayerId = @param0)))), 2)) 
                <= (SELECT Biology FROM Players AS Players_1 WHERE (PlayerId = @param0)) / 2) AND 
                (SystemID <= (SELECT LastOrginID FROM Configuration))", new List<object>() { pId });
       }
       public static DataSet GetMessagesForPlayer(int pId)
       {
           return DAL.GetInstance().GetData(@"SELECT MessageId, (SELECT PlayerName FROM Players WHERE (PlayerId = Messages.FromPId)) AS ""From"", (SELECT PlayerName FROM Players AS Players_1 WHERE (PlayerId = Messages.ToPId)) AS ""To"", 
                Msg as ""Message"", Sent 
                FROM Messages WHERE (ToPId = @param0 ) Order by Sent desc", new List<object>(){pId});
       }
       public static DataSet GetPlanetData(int sysId, int planetId, int ownerId)
       {
           return DAL.GetInstance().GetData(@"SELECT Planets.PlanetID, Planets.SystemID, Planets.OwnerID, Planets.FleetOwnerID, 
                Planets.ColonyShips, Planets.Transports, Planets.Destroyers, Planets.Crusiers, Planets.Battleships, Planets.Farms, 
                Planets.Factories, Planets.Cybernets, Planets.Labs, Planets.Starbase, Planets.PopulationLevel, Planets.PopToNextLevel, Planets.ProductionPoints, Systems.SystemName 
                FROM Planets INNER JOIN Systems ON Planets.SystemID = Systems.SystemID WHERE (Planets.PlanetID = @param0) AND (Planets.SystemID = @param1) AND (Planets.OwnerID = @param2)",
                                                                                                                                                                                           new List<object>() { planetId, sysId, ownerId });
       }
       public static DataSet GetPlanets(int pId)
       {
           return DAL.GetInstance().GetData(@"SELECT Planets.PlanetID, Planets.SystemID, Planets.OwnerID, Planets.FleetOwnerID, Planets.Farms, Planets.Factories, Planets.Cybernets, Planets.Labs, Planets.Starbase, Planets.PopulationLevel, Planets.PopToNextLevel, Planets.ProductionPoints, Systems.SystemName + ' #' + CAST(Planets.PlanetID AS nvarchar) AS Expr1 FROM Planets INNER JOIN Systems ON Planets.SystemID = Systems.SystemID WHERE (Planets.OwnerID = @param0) ORDER BY Planets.PopulationLevel DESC, Planets.PopToNextLevel",
               new List<object>() { pId });
       }
       public static DataSet GetPlayerInfo(int pId)
       {
           return DAL.GetInstance().GetData(@"SELECT PlayerName, ExpPoints, OrginID, CurrentCulture FROM Players WHERE (PlayerId = @param0)", new List<object>() { pId });
       }
       public static DataSet GetPlayerIntel(int pId, int activePlayer)
       {
           return DAL.GetInstance().GetData(@"SELECT Attack, Defense, Speed, Culture, Production, Science, Growth, Biology, Economy, Energy, Math, Physics, Social FROM Players WHERE (PlayerId = @param0) AND (@param0 = @param1) OR (PlayerId = @param0) AND ((SELECT Biology FROM Players AS Players_1 WHERE (PlayerId = @param1)) >= Biology + (6 -
                          (SELECT     Academy
                            FROM          Players AS Players_2
                            WHERE      (PlayerId = @param1))))", new List<object>() { pId, activePlayer });
       }
       public static DataSet GetSciences(int pId)
       {
           return DAL.GetInstance().GetData(@"SELECT Biology, Economy, Energy, Math, Physics, Social, CurrentCulture FROM Players WHERE (PlayerId = @param0)", new List<object>() { pId });
       }
       public static DataSet GetSystemView(int sId)
       {
           return DAL.GetInstance().GetData(@"SELECT Planets.PlanetID, Planets.PopulationLevel, Players.PlayerName, Players.PlayerId, CAST(Planets.Starbase AS int) AS Starbase FROM Planets INNER JOIN Players ON Planets.OwnerID = Players.PlayerId WHERE (Planets.SystemID = @param0)", new List<object>() { sId });
       }
       public static DataSet GetAllianceMembers(int aId)
       {
           return DAL.GetInstance().GetData(@"SELECT Players.PlayerName, Players.PlayerId FROM AllianceMembers INNER JOIN Players ON AllianceMembers.PlayerId = Players.PlayerId WHERE (AllianceMembers.AllianceId = @param0)", new List<object>() { aId });
       }
       public static void DeleteMessage(int MessageId)
       {
           DAL.GetInstance().DeleteData("Delete from Messages Where MessageId = @param0", new List<object>() { MessageId });
       }
    }
}
